﻿using System.Data.SQLite;

namespace TempHumidityMonitor {
    public static class DatabaseHelper {
        private const string ConnectionString = "Data Source=sensor_data.db;Version=3;";

        /// <summary>
        /// 静态构造函数，在首次使用类时执行一次
        /// </summary>
        static DatabaseHelper() {
            InitializeDatabase();
        }

        private static void InitializeDatabase() {
            using (var connection = new SQLiteConnection(ConnectionString)) {
                connection.Open();
                string createTableSql = @"
                    CREATE TABLE IF NOT EXISTS SensorData (
                        Id INTEGER PRIMARY KEY AUTOINCREMENT,
                        Temperature REAL NOT NULL,
                        Humidity REAL NOT NULL,
                        Timestamp DATETIME DEFAULT (datetime('now', 'localtime'))
                    );";
                using (var command = new SQLiteCommand(createTableSql, connection)) {
                    command.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// 保存一条数据到数据库
        /// </summary>
        public static void SaveData(float temperature, float humidity) {
            using (var connection = new SQLiteConnection(ConnectionString)) {
                connection.Open();
                string insertSql = "INSERT INTO SensorData (Temperature, Humidity) VALUES (@temp, @humi)";
                using (var command = new SQLiteCommand(insertSql, connection)) {
                    command.Parameters.AddWithValue("@temp", temperature);
                    command.Parameters.AddWithValue("@humi", humidity);
                    command.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// 查询所有历史数据
        /// </summary>
        /// <returns>包含历史数据的DataTable</returns>
        public static System.Data.DataTable GetAllData() {
            var dataTable = new System.Data.DataTable();
            using (var connection = new SQLiteConnection(ConnectionString)) {
                connection.Open();
                string selectSql = "SELECT Id, Temperature, Humidity, Timestamp FROM SensorData ORDER BY Timestamp DESC";
                using (var adapter = new SQLiteDataAdapter(selectSql, connection)) {
                    adapter.Fill(dataTable);
                }
            }
            return dataTable;
        }
    }
}